﻿using DBUtil.MetaData;
using System.Collections.Generic;
using DotNetCommon.Extensions;
using Index = DBUtil.MetaData.Index;
using DotNetCommon;
using System.Threading.Tasks;
using System.IO;

namespace DBUtil;

/// <summary>
/// 数据库管理对象
/// </summary>
public abstract class DBManage(DBAccess db)
{
    /// <summary>
    /// 数据库访问对象
    /// </summary>
    public readonly DBAccess db = db;

    protected string AddQuote(string tablePureName, string schemaPureName = null)
    {
        if (schemaPureName.IsNullOrWhiteSpace()) return db.AddQuote(tablePureName);
        else return $"{db.AddQuote(schemaPureName)}.{db.AddQuote(tablePureName)}";
    }

    #region 获取元数据信息
    /// <summary>
    /// 返回所有的架构/模式
    /// </summary>
    /// <remarks>
    /// 注意: 对于 mysql 仅返回当前数据库信息(因为db等同于schema)
    /// </remarks>
    public virtual async Task<List<Schema>> ShowSchemasAsync()
    {
        var sql = $"select * from INFORMATION_SCHEMA.SCHEMATA";
        var dt = await db.SelectDataTableAsync(sql);
        var res = new List<Schema>();
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            res.Add(new Schema()
            {
                Name = dt.Rows[i]["SCHEMA_NAME"].ToString(),
                DataBaseName = dt.Rows[i]["CATALOG_NAME"].ToString(),
                Owner = dt.Rows[i]["SCHEMA_OWNER"].ToString()
            });
        }
        return res;
    }

    #region 表的元数据
    /// <summary>
    /// 返回所有的表(简略信息,不包含 列/索引/约束/触发器等信息)<br/>
    /// <list type="bullet">
    /// <item>对于sqlserver: 如果 <c>schemaPureName</c> 为空则返回所有schema下的表</item>
    /// <item>对于mysql: 如果传入 schemaPureName 则查指定 schemaPureName 的, 如果不传则查当前db的</item>
    /// </list>
    /// </summary>
    public abstract Task<List<Table>> ShowTablesAsync(string schemaPureName = null);

    /// <summary>
    /// 返回当前schema下所有的表(简略信息,不包含 列/索引/约束/触发器等信息)
    /// </summary>
    /// <remarks>注意: 对于mysql, 效果同 <seealso cref="ShowTablesAsync(string)"/></remarks>
    public abstract Task<List<Table>> ShowCurrentSchemaTablesAsync();

    /// <summary>
    /// 返回指定表的详细元数据(包含 列/索引/约束/触发器等信息),如:
    /// <list type="bullet">
    /// <item>manage.ShowTableDetail("table1", "dbo"): 指定表所在的 schema</item>
    /// <item>manage.ShowTableDetail("table1"): 仅表名,将限定在当前数据库内</item>
    /// </list>
    /// </summary>
    public abstract Task<Table> ShowTableDetailAsync(string tablePureName, string schemaPureName = null, EnumTableDetailType detailType = EnumTableDetailType.All);

    /// <summary>
    /// 返回指定表的所有列
    /// <list type="bullet">
    /// <item>manage.ShowTableColumns("table1", "dbo"): 指定表所在的 schema, 将限定在当前数据库中</item>
    /// <item>manage.ShowTableColumns("table1"): 仅表名, 将限定在当前数据库</item>
    /// </list>
    /// </summary>
    public abstract Task<List<Column>> ShowTableColumnsAsync(string tablePureName, string schemaPureName = null);

    /// <summary>
    /// 返回指定表的所有索引
    /// <list type="bullet">
    /// <item>manage.ShowTableIndexes("table1", "dbo"): 指定表所在的 schema, 将限定在当前数据库中</item>
    /// <item>manage.ShowTableIndexes("table1"): 仅表名,将限定在当前数据库</item>
    /// </list>
    /// </summary>
    public abstract Task<List<Index>> ShowTableIndexesAsync(string tablePureName, string schemaPureName = null);

    /// <summary>
    /// 返回指定表的所有触发器
    /// <list type="bullet">
    /// <item>manage.ShowTableTriggers("table1", "dbo"): 指定表所在的 schema, 将限定在当前数据库中</item>
    /// <item>manage.ShowTableTriggers("table1"): 仅表名,将限定在当前数据库</item>
    /// </list>
    /// </summary>
    public abstract Task<List<Trigger>> ShowTableTriggersAsync(string tablePureName, string schemaPureName = null);

    /// <summary>
    /// 返回表的所有约束信息
    /// <list type="bullet">
    /// <item>manage.ShowTableConstraints("table1", "dbo"): 指定表所在的 schema, 将限定在当前数据库中</item>
    /// <item>manage.ShowTableConstraints("table1"): 仅表名,将限定在当前数据库</item>
    /// </list>
    /// </summary>
    public abstract Task<List<MetaData.Constraint>> ShowTableConstraintsAsync(string tablePureName, string schemaPureName = null);
    #endregion

    #region 视图元数据
    /// <summary>
    /// 返回用户自定义的所有视图,不含列等信息<br/>
    /// 如果 <c>schemaPureName</c> 为空则返回所有schema下的视图
    /// </summary>
    public abstract Task<List<View>> ShowViewsAsync(string schemaPureName = null);

    /// <summary>
    /// 返回当前schema下用户自定义的所有视图,不含列等信息<br/>
    /// </summary>
    /// <remarks>注意: 对于mysql,效果同 <seealso cref="ShowViewsAsync"/></remarks>
    public abstract Task<List<View>> ShowCurrentSchemaViewsAsync();

    /// <summary>
    /// 返回指定视图的详细元数据
    /// <list type="bullet">
    /// <item>manage.ShowViewDetail("view1", "dbo"): 指定视图所在的 schema, 将限定在当前数据库中</item>
    /// <item>manage.ShowViewDetail("view1"): 仅视图名,将限定在当前数据库</item>
    /// </list>
    /// </summary>
    public abstract Task<View> ShowViewDetailAsync(string viewPureName, string schemaPureName = null);
    #endregion

    #region 存储过程&函数&序列 元数据
    /// <summary>
    /// 返回所有的存储过程(仅用户自定义的)
    /// </summary>
    public abstract Task<List<Procedure>> ShowProceduresAsync(string schemaPureName = null);

    /// <summary>
    /// 返回所有的函数(仅用户自定义的)
    /// </summary>
    public abstract Task<List<Function>> ShowFunctionsAsync(string schemaPureName = null);

    /// <summary>
    /// 返回所有的序列
    /// </summary>
    public abstract Task<List<Sequence>> ShowSequencesAsync(string schemaPureName = null);
    #endregion

    #endregion

    #region 修改数据库元数据
    /// <summary>
    /// 重命名表名
    /// </summary>
    public abstract string RenameTableSql(string oldTablePureName, string newTablePureName, string schemaPureName = null);

    /// <summary>
    /// 重命名列名
    /// </summary>
    public abstract string RenameColumnSql(string tablePureName, string oldPureName, string newPureName, string schemaPureName = null);

    /// <summary>
    /// 修改表说明
    /// </summary>
    public abstract string UpdateTableDescriptionSql(string tablePureName, string description, string schemaPureName = null);

    /// <summary>
    /// 修改视图说明
    /// </summary>
    public abstract string UpdateViewDescriptionSql(string viewPureName, string description, string schemaPureName = null);

    /// <summary>
    /// 修改列说明
    /// </summary>
    public abstract Task<string> UpdateColumnDescriptionSql(string tablePureName, string colPureName, string description, string schemaPureName = null);

    /// <summary>
    /// 给指定表添加一列
    /// </summary>
    public abstract string AddColumnSql(string tablePureName, string schemaPureName = null);

    /// <summary>
    /// 删除指定表的指定列
    /// </summary>
    public abstract string DropColumnSql(string tablePureName, string columnPureName, string schemaPureName = null);

    /// <summary>
    /// 删除指定表
    /// </summary>
    public virtual string DropTableSql(string tablePureName = null, string schemaPureName = null)
    {
        AssertUtil.NotNullOrWhiteSpace(tablePureName);
        if (schemaPureName.IsNullOrWhiteSpace())
        {
            return $"drop table {db.AddQuote(tablePureName)}";
        }
        else
        {
            return $"drop table {db.AddQuote(schemaPureName)}.{db.AddQuote(tablePureName)}";
        }
    }

    /// <summary>
    /// 删除指定表
    /// </summary>
    public abstract string DropTableIfExistSql(string tablePureName, string schemaPureName = null);

    /// <summary>
    /// 修改列是否可为空
    /// </summary>
    public abstract Task<string> UpdateColumnNullAbleSql(string tablePureName, string colPureName, bool isNullAble, string schemaPureName = null);

    /// <summary>
    /// 修改列是否唯一
    /// </summary>
    public abstract Task<string> UpdateColumnUniqueSql(string tablePureName, string colPureName, bool isUnique, string schemaPureName = null);

    /// <summary>
    /// 修改列是否自增
    /// </summary>
    public abstract Task<string> UpdateColumnIdentitySql(string tablePureName, string colPureName, bool isIdentity, string schemaPureName = null);

    /// <summary>
    /// 修改列类型
    /// </summary>
    public abstract Task<string> UpdateColumnTypeSql(string tablePureName, string colPureName, string typeString, string schemaPureName = null);

    /// <summary>
    /// 修改列
    /// </summary>
    public abstract Task<string> UpdateColumnSql(string tablePureName, string colPureName, string schemaPureName = null);

    /// <summary>
    /// 设置列默认值
    /// </summary>
    public abstract Task<string> UpdateColumnDefaultSql(string tablePureName, string colPureName, string defaultString, string schemaPureName = null);

    /// <summary>
    /// 移除列默认值
    /// </summary>
    public abstract Task<string> RemoveColumnDefaultSql(string tablePureName, string colPureName, string schemaPureName = null);

    /// <summary>
    /// 删除触发器
    /// </summary>
    public virtual string DropTriggerSql(string triggerPureName, string schemaPureName = null)
    {
        if (schemaPureName.IsNullOrWhiteSpace())
        {
            return $"drop trigger {db.AddQuote(triggerPureName)}";
        }
        else
        {
            return $"drop trigger {db.AddQuote(schemaPureName)}.{db.AddQuote(triggerPureName)}";
        }
    }

    /// <summary>
    /// 删除触发器
    /// </summary>
    public abstract string DropTriggerIfExistSql(string triggerPureName, string schemaPureName = null);

    /// <summary>
    /// 删除存储过程
    /// </summary>
    public virtual string DropProcedureSql(string procPureName, string schemaPureName = null)
    {
        if (schemaPureName.IsNullOrWhiteSpace())
        {
            return $"drop procedure {db.AddQuote(procPureName)}";
        }
        else
        {
            return $"drop procedure {db.AddQuote(schemaPureName)}.{db.AddQuote(procPureName)}";
        }
    }

    /// <summary>
    /// 删除存储过程
    /// </summary>
    public abstract string DropProcedureIfExistSql(string procPureName, string schemaPureName = null);

    /// <summary>
    /// 修改约束
    /// </summary>
    public abstract string UpdateConstaintSql();

    /// <summary>
    /// 修改索引
    /// </summary>
    public abstract string UpdateIndexSql();

    /// <summary>
    /// 修改触发器
    /// </summary>
    public abstract string UpdateTriggerSql();

    /// <summary>
    /// 设置主键
    /// </summary>
    public abstract string SetPrimaryKeySql(string tablePureName, string schemaPureName = null, params string[] colPureNames);

    /// <summary>
    /// 删除主键
    /// </summary>
    public abstract string RemovePrimaryKeySql(string tablePureName, string schemaPureName = null);
    #endregion

    #region 生成insert语句
    /// <summary>
    /// 生成表的insert语句, 如:
    /// <code>
    /// //表名字的格式也可以是 "[dbo].[t_user]"
    /// await db.Manage.GenerateInsertSqlAsync("t_user", 200, "age>18", "order by create_time desc");
    /// </code>
    /// </summary>
    /// <inheritdoc cref="GenerateInsertSqlAsync(Stream, string, int, string, string)" path="/remarks"/>
    public abstract Task<(long exportCount, string sql, long totalCount)> GenerateInsertSqlAsync(string tableQuotedName, int limitCount = 0, string filter = null, string order = null);

    /// <summary>
    /// 生成表的insert语句, 如:
    /// <code>
    /// //表名字的格式也可以是 "[dbo].[t_user]"
    /// await db.Manage.GenerateInsertSqlFileAsync("d:\\t_user.sql", "t_user", 200, "age>18", "order by create_time desc");
    /// </code>
    /// </summary>
    /// <inheritdoc cref="GenerateInsertSqlAsync(Stream, string, int, string, string)" path="/remarks"/>
    public abstract Task<long> GenerateInsertSqlFileAsync(string fileAbsPath, string tableQuotedName, int limitCount = 0, string filter = null, string order = null);

    /// <summary>
    /// 生成表的insert语句, 如:
    /// <code>
    /// //表名字的格式也可以是 "[dbo].[t_user]"
    /// await db.Manage.GenerateInsertSqlAsync(new FileStream("d:\\t_user.sql"), "t_user", 200, "age>18", "order by create_time desc");
    /// </code>
    /// </summary>
    /// <remarks>
    /// 注意:
    /// 生成的sql示例:
    /// <code>
    /// // 一行时
    /// insert into [t_user]([id],[name]) values(1,'jack');
    /// 
    /// //多行时
    /// insert into [t_user]([id],[name]) values
    ///     (1,'jack')
    ///     ,(2,'tom');
    /// </code>
    /// </remarks>
    public abstract Task<long> GenerateInsertSqlAsync(Stream stream, string tableQuotedName, int limitCount = 0, string filter = null, string order = null);
    #endregion

    #region truncate指定表
    public virtual string TruncateTableSql(string tablePureName, string schemaPureName = null)
    {
        AssertUtil.NotNullOrWhiteSpace(tablePureName);
        return $"truncate table {AddQuote(tablePureName, schemaPureName)}";
    }
    #endregion

    #region 获取记录数
    public virtual long GetCount(string tableQuotedName, string filter = null, IDictionary<string, object> parameters = null)
    {
        AssertUtil.NotNullOrWhiteSpace(tableQuotedName);
        var sql = $"select count(1) from {tableQuotedName} {db.GenerateWhereClause(filter)}";
        return db.SelectScalar<long>(sql, parameters);
    }
    public virtual async Task<long> GetCountAsync(string tableQuotedName, string filter = null, IDictionary<string, object> parameters = null)
    {
        AssertUtil.NotNullOrWhiteSpace(tableQuotedName);
        var sql = $"select count(1) from {tableQuotedName} {db.GenerateWhereClause(filter)}";
        return await db.SelectScalarAsync<long>(sql, parameters);
    }
    #endregion

    #region 生成建表脚本
    /// <summary>
    /// 获取生成表的sql语句
    /// </summary>
    public abstract Task<string> GenerateCreateTableSqlAsync(string tablePureName, string schemaPureName = null);

    /// <summary>
    /// 批量获取生成表的sql语句, 如:
    /// <code>
    /// //表名也可以是 "[dbo].[t_user]"
    /// await db.Manage.GenerateCreateTableSqlBatchAsync(new FileStream("d:\\t_user.sql"), new string[] {"t_user", "t_person"})
    /// </code>
    /// </summary>
    /// <remarks>
    /// 注意: 对于 mysql 就是 show create table `t_user`
    /// </remarks>
    public abstract Task GenerateCreateTableSqlBatchAsync(Stream stream, IEnumerable<string> tableQuotedNames, bool includeInsertSql = false);
    #endregion

    #region GenerateCodeAsync
    public abstract Task<string> GenerateCodeAsync(string tablePureName, string schemaPureName);
    #endregion
}
